package com.example;

import cn.hutool.core.codec.Base64;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.Page;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.setting.Setting;
import com.example.domain.ExcelData;
import com.example.uitl.ExcelUtil;
import com.example.uitl.PhoneNumEncrypt;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.UnsupportedEncodingException;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.atomic.AtomicLong;
import java.util.stream.Collectors;

/**
 * @ClassName Main
 * @Description TODO
 * @Author: XMK
 * @Date: 2021/11/4 10:20
 * @Version: 1.0
 */
public class Main {
    public static void main(String[] args) throws SQLException {

        Setting set = new Setting("db.setting", true);;
        DSFactory dsFacSqlserver = DSFactory.create(set);
        Setting set1 = new Setting("db1.setting", true);;
        DSFactory dsFacOra = DSFactory.create(set1);

        long maxid = 2702003;
//        long maxid = 655265;
        long id = 0;
//        long id = 1915466;
        List<Entity> data = null;
        AtomicLong xh = new AtomicLong(0);
        while (id < maxid) {
            System.out.println("id::"+id);
            try {
                data = Db.use(dsFacSqlserver.getDataSource()).query("select  * from T_ODCFZYZ_LOG where id in(select id from (select aa.* ,row_number() over (partition by oid order by id) rn from(select t1.id,t1.oid from T_ODCFZYZ_LOG t1 where VerifyTime>='2018-01-01' and VerifyTime<'2021-01-01' and VerifyResult='0') aa ) cc where  rn=1 ) and id > ? ORDER BY VerifyTime", id);
                if (data.size() < 1) {
                    break;
                }
            } catch (Exception e) {
                e.printStackTrace();
                continue;
            }

            Long newid = data.get(data.size() - 1).getLong("id");
            System.out.println("new id::"+newid);
            id = newid > maxid ? maxid : newid;
            List<ExcelData> collect = data.stream().map(e -> {
                ExcelData excelData = new ExcelData();
                excelData.setXh(xh.incrementAndGet());
                excelData.setYydw("深圳市监局");
                excelData.setEntname(e.getStr("verifymsg").replace("验证通过: jgmc:", ""));
                String oid = e.getStr("oid");
                excelData.setOid(oid);
                excelData.setSysj(e.getDate("verifytime"));
                excelData.setCert(e.getStr("cert"));

                try {
                    String ajgmd = Base64.decodeStr(oid.substring(oid.indexOf("ZZ1") + 3)).trim();
                    List<Entity> odcinfos = Db.use(dsFacOra.getDataSource()).query("select * from t_odcinfo where jgdm=?", ajgmd);
                    if (odcinfos.size() < 1) {
                        odcinfos = Db.use(dsFacOra.getDataSource()).query("select * from t_odcinfo where oid=?", e.getStr("oid"));
                        if (odcinfos.size() < 1) {
                            odcinfos = Db.use(dsFacOra.getDataSource()).query("select * from t_odcinfo where jgmc=?", e.getStr("verifymsg").replace("验证通过: jgmc:", ""));
                            if (odcinfos.size() < 1) {
                                System.out.println("odcinfos 空：LOG id=" + e.getLong("id"));
                                return excelData;
                            }
                        }
                    }
                    Entity odcinfo = odcinfos.get(0);

                    excelData.setSerialno(odcinfo.getStr("certsn"));
                    excelData.setZcsj(odcinfo.getDate("bfsj"));
                    String cadm = odcinfo.getStr("cadm");
                    if ("0".equals(cadm)) {
                        excelData.setCa("SZCA");
                    } else if ("1".equals(cadm)) {
                        excelData.setCa("GDCA");
                    } else if ("2".equals(cadm)) {
                        excelData.setCa("NETCA");
                    } else if ("3".equals(cadm)) {
                        excelData.setCa("BJCA");
                    } else {
                        excelData.setCa(cadm);
                    }

                    List<Entity> jgdms = Db.use(dsFacOra.getDataSource()).query("select socialcode,fddbr,FRDH,TBRMC,TBRLXDH from t_jgdms where jgdm=?", odcinfo.getStr("jgdm"));
                    if (jgdms.size() < 1) {
                        List<Entity> ents = Db.use(dsFacOra.getDataSource()).query("select socialcode,lerepcode,FDDBRSJ,jbrname,jbrlxdh  from t_entreginfo where jgdm=?", odcinfo.getStr("jgdm"));
                        if (ents.size() < 1) {
                            System.out.println("jgdms 空：jgdm =" + odcinfo.getStr("jgdm"));
                            return excelData;
                        } else {
                            Entity jgdminfo = ents.get(0);
                            excelData.setFddbr(jgdminfo.getStr("lerepcode"));
                            excelData.setFddh(PhoneNumEncrypt.decrypt(jgdminfo.getStr("FDDBRSJ")));
                            excelData.setJbr(jgdminfo.getStr("jbrname"));
                            excelData.setJbrdh(PhoneNumEncrypt.decrypt(jgdminfo.getStr("jbrlxdh")));
                            excelData.setSocialcode(jgdminfo.getStr("socialcode"));
                        }
                    } else {
                        Entity jgdminfo = jgdms.get(0);
                        excelData.setFddbr(jgdminfo.getStr("fddbr"));
                        excelData.setFddh(PhoneNumEncrypt.decrypt(jgdminfo.getStr("frdh")));
                        excelData.setJbr(jgdminfo.getStr("tbrmc"));
                        excelData.setJbrdh(PhoneNumEncrypt.decrypt(jgdminfo.getStr("tbrlxdh")));
                        excelData.setSocialcode(jgdminfo.getStr("socialcode"));
                    }

                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                return excelData;
            }).collect(Collectors.toList());

            ExcelUtil<ExcelData> util = new ExcelUtil<>(ExcelData.class);
            util.exportExcel(collect, "ODC使用");
        }

    }

    @Test
    public void test4() throws UnsupportedEncodingException {
        String oid = "00011@0006ZZ1WDE5MTI4NzY4";
        System.out.println(Base64.decodeStr("WDE5MTI4NzY4"));;
        System.out.println(oid.substring(oid.indexOf("ZZ1") + 3));;
        System.out.println(Base64.decodeStr(oid.substring(oid.indexOf("ZZ1") + 3)));;
    }

    @Test
    public void test1() throws SQLException {


        Setting set1 = new Setting("db1.setting", true);;

        DSFactory dsFactory = DSFactory.create(set1);

        List<Entity> jgdms = Db.use(dsFactory.getDataSource()).query("select socialcode,fddbr,FRDH,TBRMC,TBRLXDH from t_jgdms where jgdm=?", "795421545");

        System.out.println(jgdms);
    }

    @Test
    public void test3() throws SQLException {


        Setting set1 = new Setting("db1.setting", true);;

        DSFactory dsFactory = DSFactory.create(set1);

        List<Entity> query = Db.use(dsFactory.getDataSource()).query("select * from t_odcinfo where jgdm=?", "MA5G7GU38");

        System.out.println(query);
    }

    @Test
    public void test2() throws SQLException {


        Setting set1 = new Setting("db1.setting", true);;

        DSFactory dsFactory = DSFactory.create(set1);

        List<Entity> data = Db.use(dsFactory.getDataSource()).query("select * from t_odcinfo where jgdm=?", "MA5G7GU38");

//        System.out.println(data);
        List<ExcelData> collect = data.stream().map(e -> {
            System.out.println(e.getStr("jgmc"));
            ExcelData excelData = new ExcelData();
            excelData.setEntname(e.getStr("jgmc"));
            excelData.setCa(e.getStr("cadm"));
            excelData.setOid(e.getStr("oid"));
            excelData.setZcsj(e.getDate("bfsj"));
            excelData.setSerialno(e.getStr("certsn"));

            return excelData;
        }).collect(Collectors.toList());

        System.out.println(collect);

        ExcelUtil<ExcelData> util = new ExcelUtil<>(ExcelData.class);
        util.exportExcel(collect, "ODC使用");


    }

}
